package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.ordermanager.model.ServicePoint;
import com.eling.elcms.travel.dao.IRoomStatisticsDao;
import com.eling.elcms.travel.model.NonMemberGroup;
import com.eling.elcms.travel.model.view.*;
import org.springframework.stereotype.Repository;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Repository
public class RoomStatisticsDaoHibernate extends GenericDaoHibernate<ServicePoint, Long> implements IRoomStatisticsDao {

	public RoomStatisticsDaoHibernate() {
		super(ServicePoint.class);
	}

	@SuppressWarnings({ "unchecked", "unused" })
	@Override
	public List<RoomStatisticsView> queryAllByDate(Date startDate, Date endDate, ServicePoint servicePoint) {

		StringBuffer sqlRt = new StringBuffer();// 查询所有房型列表

		sqlRt.append(" SELECT tr.`name`,COUNT(br.code),tr.pkRoomType           ");
		sqlRt.append("  FROM travel_roomtype tr             ");
		sqlRt.append(" LEFT JOIN bd_room br                 ");
		sqlRt.append(" ON br.pkroomtype=tr.pkroomtype       ");
		sqlRt.append(" LEFT JOIN                            ");
		sqlRt.append("   om_servicepoint os                 ");
		sqlRt.append(" ON                                   ");
		sqlRt.append(" os.pkServicePoint=tr.pkServicePoint  ");
		sqlRt.append(" WHERE                                ");
		sqlRt.append(" os.pkServicePoint=:pkServicePoint    ");
		sqlRt.append(" GROUP BY                             ");
		sqlRt.append(" tr.pkRoomType                        ");
		
		List<Object[]> rTypeList = getSession().createNativeQuery(sqlRt.toString())
				.setParameter("pkServicePoint", servicePoint.getPkServicePoint()).list();

		List<Object[]> roomTypeList = new ArrayList<>();
		//如果有房型，而该房型的房间数量为0的，被排除掉
		for(int i = 0; i < rTypeList.size(); i++){
			Object[] objects = rTypeList.get(i);
			if(Integer.parseInt(objects[1].toString()) != 0){
				roomTypeList.add(objects);
			}
		}
		
		List<List<Object>> columnList = new ArrayList<>();
		for (int i = 0; i < 15; i++) {

			List<Object> oList = new ArrayList<>();
			List<Object> iList = new ArrayList<>();
			List<Object> mList = new ArrayList<>();
			List<Object> aList = new ArrayList<>();

			StringBuffer sqlGroup = new StringBuffer();// 组团
			Calendar cal = Calendar.getInstance();
			cal.setTime(startDate);
			long time1 = cal.getTimeInMillis();
			long time2 = time1 + i * (1000 * 3600 * 24L);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String sd = sdf.format(time2);
			Date date = null;
			try {
				date = sdf.parse(sd);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			sqlGroup.append("  SELECT trt.pkRoomType,COUNT(*)            ");
			sqlGroup.append("  FROM                                      ");
			sqlGroup.append(" travel_groupreserve tgr                    ");
			sqlGroup.append(" LEFT JOIN travel_group_roomregister tgrr   ");
			sqlGroup.append(" ON tgrr.pkGroupReserve=tgr.pkGroupReserve  ");
			sqlGroup.append(" LEFT JOIN travel_roomregister trr          ");
			sqlGroup.append(" ON trr.pkRoomRegister=tgrr.pkRoomRegister  ");
			sqlGroup.append(" LEFT JOIN bd_room br                       ");
			sqlGroup.append(" ON br.pkRoom=trr.pkRoom                    ");
			sqlGroup.append(" LEFT JOIN travel_roomtype trt              ");
			sqlGroup.append(" ON trt.pkRoomType=br.pkRoomType            ");
			sqlGroup.append(" LEFT JOIN om_servicepoint os               ");
			sqlGroup.append(" ON os.pkServicePoint=trt.pkServicePoint    ");
			sqlGroup.append(" LEFT JOIN travel_planitemgroup tptg ON tptg.pkPlanItemGroup=tgr.pkPlanItemGroup ");
			sqlGroup.append("  WHERE                                     ");
			sqlGroup.append("  	os.pkServicePoint =:pkServicePoint       ");
			//如果当天入住当天退住，算一天。如果大于一天的，最后一天不算在内
			sqlGroup.append(" AND CASE WHEN tgr.checkInDate = :nowDate AND tgr.checkInDate = tgr.checkOutDate ");
			sqlGroup.append(" THEN 1 ELSE tgr.checkInDate <= :nowDate  AND tgr.checkOutDate > :nowDate END  ");
			//sqlGroup.append(" AND tgr.checkInDate <=:nowDate             ");
			//sqlGroup.append("  AND tgr.checkOutDate >:nowDate           ");
			sqlGroup.append(" AND tptg.checkInStatus != 'Initial'    ");
			sqlGroup.append("  GROUP BY                                  ");
			sqlGroup.append(" trt.pkRoomType                             ");

			List<Object[]> GroupList = getSession().createNativeQuery(sqlGroup.toString()).setParameter("nowDate", date)
					.setParameter("pkServicePoint", servicePoint.getPkServicePoint()).list();

			for (int j = 0; j < roomTypeList.size(); j++) {
				Object[] objects = roomTypeList.get(j);
				for (int k = 0; k < GroupList.size(); k++) {
					Object[] objects2 = GroupList.get(k);
					if ((objects[2] == null ? 0 : Integer.parseInt(objects[2].toString())) == (objects2[0] == null ? 0
							: Integer.parseInt(objects2[0].toString()))) {
						oList.add(objects2[1]);
					}
				}
				if (oList.size() <= j) {
					oList.add(0);
				}
			}
			StringBuffer sqlNon = new StringBuffer();// 散客

			sqlNon.append(" SELECT trt.pkRoomType,COUNT(*),trt.`name`                    ");
			sqlNon.append(" FROM                                                         ");
			sqlNon.append(" travel_nonmemgroupreserve tngr                               ");
			sqlNon.append(" LEFT JOIN                                                    ");
			sqlNon.append(" travel_nonmembergroup tnm                                    ");
			sqlNon.append(" ON tnm.pkNonMemberGroup=tngr.pkNonMemberGroup                ");
			sqlNon.append(" LEFT JOIN                                                    ");
			sqlNon.append(" travel_signuproom tsr                                        ");
			sqlNon.append(" ON tsr.pkNonMemGroupReserve=tngr.pkNonMemGroupReserve        ");
			sqlNon.append(" LEFT JOIN                                                    ");
			sqlNon.append(" travel_roomtype trt                                          ");
			sqlNon.append(" ON trt.pkRoomType=tsr.pkRoomType                             ");
			sqlNon.append(" LEFT JOIN                                                    ");
			sqlNon.append(" om_servicepoint os                                           ");
			sqlNon.append(" ON os.pkServicePoint=trt.pkServicePoint                      ");
			sqlNon.append(" WHERE                                                        ");
			sqlNon.append(" os.pkServicePoint=:pkServicePoint                            ");
			//如果当天入住当天退住，算一天。如果大于一天的，最后一天不算在内
			sqlNon.append(" AND CASE WHEN tnm.checkInDate = :nowDate AND tnm.checkInDate = tnm.checkOutDate ");
			sqlNon.append(" THEN 1 ELSE tnm.checkInDate <= :nowDate  AND tnm.checkOutDate > :nowDate END  ");
			//sqlNon.append(" AND tnm.checkInDate<=:nowDate AND tnm.checkOutDate>:nowDate ");
			sqlNon.append(" AND tnm.`status` IN ('"+NonMemberGroup.Status.CheckIn+"','"+NonMemberGroup.Status.CheckOut+"','"+NonMemberGroup.Status.Reserve+"') ");
			sqlNon.append(" GROUP BY                                                     ");
			sqlNon.append(" trt.pkRoomType                                               ");
			List<Object[]> nonList = getSession().createNativeQuery(sqlNon.toString()).setParameter("nowDate", date)
					.setParameter("pkServicePoint", servicePoint.getPkServicePoint()).list();

			for (int j = 0; j < roomTypeList.size(); j++) {
				Object[] objects = roomTypeList.get(j);
				for (int k = 0; k < nonList.size(); k++) {
					Object[] objects2 = nonList.get(k);
					if ((objects[2] == null ? 0 : Integer.parseInt(objects[2].toString())) == (objects2[0] == null ? 0
							: Integer.parseInt(objects2[0].toString()))) {
						iList.add(objects2[1]);
					}
				}
				if (iList.size() <= j) {
					iList.add(0);
				}
			}
			
			//散客预约时，未入住，只预约房型及数量的情况。
			StringBuffer nonMemSql = new StringBuffer();
			
			nonMemSql.append(" SELECT                                                                                  ");
			nonMemSql.append(" tsr.pkRoomType,                                                                         ");
			nonMemSql.append(" sum(tsr.count),                                                                         ");
			nonMemSql.append(" trt.`name`                                                                              ");
			nonMemSql.append("  FROM travel_nonmemgroupreserve tngr                                                    ");
			nonMemSql.append(" LEFT JOIN travel_nonmembergroup tng ON tngr.pkNonMemberGroup = tng.pkNonMemberGroup     ");
			nonMemSql.append(" LEFT JOIN travel_signuproom tsr ON tsr.pkNonMemGroupReserve = tngr.pkNonMemGroupReserve ");
			nonMemSql.append(" left JOIN travel_roomtype  trt on trt.pkRoomType = tsr.pkRoomType                       ");
			nonMemSql.append(" LEFT JOIN                                                   								 ");
			nonMemSql.append(" om_servicepoint os                                        							   ");
			nonMemSql.append(" ON os.pkServicePoint=trt.pkServicePoint                     								 ");
			nonMemSql.append(" WHERE tng.`status` NOT in ('"+NonMemberGroup.Status.CheckIn+"','"+NonMemberGroup.Status.CheckOut+"','"+NonMemberGroup.Status.Initial+"','"+NonMemberGroup.Status.Cancel+"')   ");
			nonMemSql.append(" AND os.pkServicePoint=:pkServicePoint                           						 ");
			//如果当天入住当天退住，算一天。如果大于一天的，最后一天不算在内
			nonMemSql.append(" AND CASE WHEN tng.checkInDate = :nowDate AND tng.checkInDate = tng.checkOutDate ");
			nonMemSql.append(" THEN 1 ELSE tng.checkInDate <= :nowDate  AND tng.checkOutDate > :nowDate END  ");
			//nonMemSql.append(" AND tng.checkInDate<=:nowDate AND tng.checkOutDate>:nowDate 							");
			nonMemSql.append(" GROUP BY trt.pkRoomType                                                                 ");
			
			List<Object[]> nonMemList = getSession().createNativeQuery(nonMemSql.toString()).setParameter("nowDate", date)
					.setParameter("pkServicePoint", servicePoint.getPkServicePoint()).list();
			
			for (int j = 0; j < roomTypeList.size(); j++) {
				Object[] objects = roomTypeList.get(j);
				for (int k = 0; k < nonMemList.size(); k++) {
					Object[] objects2 = nonMemList.get(k);
					if ((objects[2] == null ? 0 : Integer.parseInt(objects[2].toString())) == (objects2[0] == null ? 0
							: Integer.parseInt(objects2[0].toString()))) {
						mList.add(objects2[1]);
					}
				}
				if (mList.size() <= j) {
					mList.add(0);
				}
			}
			
			for (int j = 0; j < iList.size(); j++) {
				Integer a = Integer.parseInt(iList.get(j).toString()) + (Integer.parseInt(oList.get(j).toString()))
						+ (Integer.parseInt(mList.get(j).toString()));
				aList.add(a);
			}
			columnList.add(aList);
		}
		List<RoomStatisticsView> viewList = new ArrayList<>();
		for (int k = 0; k < columnList.get(0).size(); k++) {
			RoomStatisticsView roomStatisticsView = new RoomStatisticsView();
			for (int i = 0; i < columnList.size(); i++) {
				List<Object> list = columnList.get(i);
				Object[] objects = roomTypeList.get(k);
				roomStatisticsView.setRoomtypename(objects[0] == null ? "" : objects[0].toString());
				roomStatisticsView.setRoomcount(objects[1] == null ? 0 : Integer.parseInt(objects[1].toString()));
				roomStatisticsView.setPkRoomType(objects[2] == null ? null : Long.valueOf(objects[2].toString()));
				for (int j = 0; j < list.size(); j++) {
					if (i == 0) {
						roomStatisticsView.setDayOne(Integer.parseInt(list.get(k).toString()));
					} else if (i == 1) {
						roomStatisticsView.setDayTwo(Integer.parseInt(list.get(k).toString()));
					} else if (i == 2) {
						roomStatisticsView.setDayThree(Integer.parseInt(list.get(k).toString()));
					} else if (i == 3) {
						roomStatisticsView.setDayFour(Integer.parseInt(list.get(k).toString()));
					} else if (i == 4) {
						roomStatisticsView.setDayFive(Integer.parseInt(list.get(k).toString()));
					} else if (i == 5) {
						roomStatisticsView.setDaySix(Integer.parseInt(list.get(k).toString()));
					} else if (i == 6) {
						roomStatisticsView.setDaySeven(Integer.parseInt(list.get(k).toString()));
					} else if (i == 7) {
						roomStatisticsView.setDayEight(Integer.parseInt(list.get(k).toString()));
					} else if (i == 8) {
						roomStatisticsView.setDayNine(Integer.parseInt(list.get(k).toString()));
					} else if (i == 9) {
						roomStatisticsView.setDayTen(Integer.parseInt(list.get(k).toString()));
					} else if (i == 10) {
						roomStatisticsView.setDayEleven(Integer.parseInt(list.get(k).toString()));
					} else if (i == 11) {
						roomStatisticsView.setDayTwelve(Integer.parseInt(list.get(k).toString()));
					} else if (i == 12) {
						roomStatisticsView.setDayThirdteen(Integer.parseInt(list.get(k).toString()));
					} else if (i == 13) {
						roomStatisticsView.setDayFourteen(Integer.parseInt(list.get(k).toString()));
					} else if (i == 14) {
						roomStatisticsView.setDayFifteen(Integer.parseInt(list.get(k).toString()));
					}
					break;
				}
			}
			viewList.add(roomStatisticsView);
		}
		RoomStatisticsView roomStatisticsView = new RoomStatisticsView();
		int count = 0;
		for (int i = 0; i < roomTypeList.size(); i++) {
			Object[] objects = roomTypeList.get(i);
			count += Integer.parseInt(objects[1].toString());
		}
		roomStatisticsView.setRoomtypename("合计");
		roomStatisticsView.setRoomcount(count);
		for (int i = 0; i < columnList.size(); i++) {
			List<Object> list = columnList.get(i);
			int number = 0;
			for (int j = 0; j < list.size(); j++) {
				number += Integer.parseInt(list.get(j).toString());
			}
			if (i == 0) {
				roomStatisticsView.setDayOne(number);
			} else if (i == 1) {
				roomStatisticsView.setDayTwo(number);
			} else if (i == 2) {
				roomStatisticsView.setDayThree(number);
			} else if (i == 3) {
				roomStatisticsView.setDayFour(number);
			} else if (i == 4) {
				roomStatisticsView.setDayFive(number);
			} else if (i == 5) {
				roomStatisticsView.setDaySix(number);
			} else if (i == 6) {
				roomStatisticsView.setDaySeven(number);
			} else if (i == 7) {
				roomStatisticsView.setDayEight(number);
			} else if (i == 8) {
				roomStatisticsView.setDayNine(number);
			} else if (i == 9) {
				roomStatisticsView.setDayTen(number);
			} else if (i == 10) {
				roomStatisticsView.setDayEleven(number);
			} else if (i == 11) {
				roomStatisticsView.setDayTwelve(number);
			} else if (i == 12) {
				roomStatisticsView.setDayThirdteen(number);
			} else if (i == 13) {
				roomStatisticsView.setDayFourteen(number);
			} else if (i == 14) {
				roomStatisticsView.setDayFifteen(number);
			}
		}
		viewList.add(roomStatisticsView);

		return viewList;
	}

	/*
	 * 入住类型收入统计
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<IncomingByRoomTypeView> queryByRoomType(Date startDate, Date endDate) {

		StringBuffer sql = new StringBuffer();
		sql.append(
				"   SELECT t.alliance,count(*), sum(t.roompay),sum(t.foodpay),sum(t.healthpay),sum(t.coldWater),										");
		sql.append(
				"	sum(t.electricpay),sum(t.trafficpay),sum(t.pickuppay),sum(t.otherpay),sum(t.sum),sum(t.hotWater),sum(t.waterOther) FROM  ");
		sql.append(
				"   (                                                                                                                                  ");
		sql.append(
				"   SELECT tpg.alliance,tci.statementDate,tgr.checkInDate,tgr.checkOutDate,		COUNT(trp.pkCIImplement ) as count,                   ");
		sql.append(
				"   sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END) AS roompay,                                                               ");
		sql.append(
				"   sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END) AS foodpay,                                                               ");
		sql.append(
				"   sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END) AS healthpay,                                                         ");
		sql.append(
				"   sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END) AS coldWater,                                                            ");
		sql.append(
				"   sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END) AS electricpay,                                                   ");
		sql.append(
				"   sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END) AS trafficpay,                                                      ");
		sql.append(
				"   sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END) AS pickuppay,                                                         ");
		sql.append(
				"   sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END) AS otherpay,                                                            ");
		sql.append(
				"    sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END)+sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END)                 ");
		sql.append(
				"   +sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END)+sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END)   ");
		sql.append(" +sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END) +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END)  ");
		sql.append(
				"   +sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END)+sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END)   ");
		sql.append(
				"   +sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END)+sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END) as sum ,   ");
		sql.append(
				"   sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) AS hotWater ,                                                        ");
		sql.append(" sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) AS waterOther  ");
		sql.append(
				"    FROM travel_roompersonalInfo trp                                                                                                  ");
		sql.append(
				"    JOIN                                                                                                                              ");
		sql.append(
				"    travel_checkoutsettlement tcs                                                                                                     ");
		sql.append(
				"   ON tcs.pkCheckOutSettlement=trp.pkCheckOutSettlement                                                                               ");
		sql.append(
				"    JOIN travel_checkinimplement tci                                                                                                  ");
		sql.append(
				"    ON tci.pkCIImplement=trp.pkCIImplement                                                                                            ");
		sql.append(
				"   JOIN travel_groupreserve tgr                                                                                                       ");
		sql.append(
				"   ON tgr.pkGroupReserve=tci.pkGroupReserve                                                                                           ");
		sql.append(
				"     JOIN travel_planitemgroup tpg                                                                                                    ");
		sql.append(
				"     ON tpg.pkPlanItemGroup=tgr.pkPlanItemGroup                                                                                       ");
		sql.append(
				"     WHERE tci.statementDate >=:startDate   AND tci.statementDate<=:endDate  AND tcs.checkOutSettlement=1                        ");
		sql.append(
				"   GROUP BY trp.pkCIImplement                                                                                                         ");
		sql.append(
				"    ) t                                                                                                                               ");
		sql.append(
				"    GROUP BY t.alliance                                                                                                               ");
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("startDate", startDate)
				.setParameter("endDate", endDate).list();
		List<IncomingByRoomTypeView> viewList = new ArrayList<>();
		List<IncomingByRoomTypeView> examList = new ArrayList<>();
		Object[] objs = new Object[] { "true", "false" };
		for (int i = 0; i < objs.length; i++) {
			IncomingByRoomTypeView incomingByRoomTypeView = new IncomingByRoomTypeView();
			for (int j = 0; j < objList.size(); j++) {
				Object[] objects = objList.get(j);
				if ((objs[i].toString()).equals(objects[0].toString())) {
					incomingByRoomTypeView.setCheckinType(objs[i].toString());
					incomingByRoomTypeView.setCount(objects[1] == null ? 0 : Integer.parseInt(objects[1].toString()));
					incomingByRoomTypeView
							.setRoomPay(objects[2] == null ? 0 : Double.parseDouble(objects[2].toString()));
					incomingByRoomTypeView
							.setFoodPay(objects[3] == null ? 0 : Double.parseDouble(objects[3].toString()));
					incomingByRoomTypeView
							.setHealthPay(objects[4] == null ? 0 : Double.parseDouble(objects[4].toString()));
					incomingByRoomTypeView
							.setWaterPay((objects[5] == null ? 0 : Double.parseDouble(objects[5].toString()))
									+(objects[11] == null ? 0 : Double.parseDouble(objects[11].toString()))
									+(objects[12] == null ? 0 : Double.parseDouble(objects[12].toString())));
					incomingByRoomTypeView
							.setElectricPay(objects[6] == null ? 0 : Double.parseDouble(objects[6].toString()));
					incomingByRoomTypeView
							.setTrafficPay(objects[7] == null ? 0 : Double.parseDouble(objects[7].toString()));
					incomingByRoomTypeView
							.setPickupPay(objects[8] == null ? 0 : Double.parseDouble(objects[8].toString()));
					incomingByRoomTypeView
							.setOtherPay(objects[9] == null ? 0 : Double.parseDouble(objects[9].toString()));
					incomingByRoomTypeView
							.setAllPay(objects[10] == null ? 0 : Double.parseDouble(objects[10].toString()));
					examList.add(incomingByRoomTypeView);
				}
			}
			if (incomingByRoomTypeView.getCheckinType() == null) {
				incomingByRoomTypeView.setCheckinType(objs[i].toString());
				incomingByRoomTypeView.setCount(0);
				incomingByRoomTypeView.setRoomPay(0.0);
				incomingByRoomTypeView.setFoodPay(0.0);
				incomingByRoomTypeView.setHealthPay(0.0);
				incomingByRoomTypeView.setWaterPay(0.0);
				incomingByRoomTypeView.setElectricPay(0.0);
				incomingByRoomTypeView.setTrafficPay(0.0);
				incomingByRoomTypeView.setPickupPay(0.0);
				incomingByRoomTypeView.setOtherPay(0.0);
				incomingByRoomTypeView.setAllPay(0.0);
				examList.add(incomingByRoomTypeView);
			}
		}
		for (int i = 0; i < examList.size(); i++) {
			IncomingByRoomTypeView incomingByRoomTypeView = examList.get(i);
			if ("true".equals(incomingByRoomTypeView.getCheckinType())) {
				incomingByRoomTypeView.setCheckinType("联盟团");
				viewList.add(incomingByRoomTypeView);
			}
			if ("false".equals(incomingByRoomTypeView.getCheckinType())) {
				incomingByRoomTypeView.setCheckinType("会员团");
				viewList.add(incomingByRoomTypeView);
			}
		}

		StringBuffer sql1 = new StringBuffer();
		sql1.append(" SELECT tng.tourType,COUNT(*),");
		sql1.append(
				" sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END) AS roompay,                                                              ");
		sql1.append(
				" sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END) AS foodpay,                                                              ");
		sql1.append(
				" sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END) AS healthpay,                                                        ");
		sql1.append(
				" sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)                                                            ");
		sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) ");
		sql1.append(" +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) AS waterpay, ");
		sql1.append(
				" sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END) AS electricpay,                                                  ");
		sql1.append(
				" sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END) AS trafficpay,                                                     ");
		sql1.append(
				" sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END) AS pickuppay,                                                        ");
		sql1.append(
				" sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END) AS otherpay,                                                           ");
		sql1.append(
				"  sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END)+sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END)                ");
		sql1.append(
				" +sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END)+sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)          ");
		sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) ");
		sql1.append(
				" +sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END)+sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END)  ");
		sql1.append(
				" +sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END)+sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END)          ");
		sql1.append(
				" FROM travel_roompersonalInfo trp                                                                          ");
		sql1.append(
				"  JOIN                                                                                                     ");
		sql1.append(
				" travel_checkoutsettlement tcs                                                                             ");
		sql1.append(
				"                                                                                                           ");
		sql1.append(
				" ON tcs.pkCheckOutSettlement=trp.pkCheckOutSettlement                                                      ");
		sql1.append(
				"  JOIN travel_checkinimplement tci                                                                         ");
		sql1.append(
				" ON tci.pkCIImplement=trp.pkCIImplement                                                                    ");
		sql1.append(
				" JOIN travel_nonmemgroupreserve tngr                                                                       ");
		sql1.append(
				" ON tngr.pkNonMemGroupReserve=tci.pkNonMemGroupReserve                                                     ");
		sql1.append(
				" JOIN travel_nonmembergroup tng                                                                            ");
		sql1.append(
				" ON tng.pkNonMemberGroup=tngr.pkNonMemberGroup                                                             ");
		sql1.append(
				" WHERE tci.statementDate >=:startDate AND tci.statementDate<=:endDate                                      ");
		sql1.append(
				" group by                                                                                                  ");
		sql1.append(
				" tng.tourType                                                                                              ");

		List<Object[]> objsList = getSession().createNativeQuery(sql1.toString()).setParameter("startDate", startDate)
				.setParameter("endDate", endDate).list();
		Object[] obj = new Object[] { "IndivMember", "TravelGroup", "Individual" };
		List<IncomingByRoomTypeView> testList = new ArrayList<>();
		for (int i = 0; i < obj.length; i++) {
			IncomingByRoomTypeView incomingByRoomTypeView = new IncomingByRoomTypeView();
			for (int j = 0; j < objsList.size(); j++) {
				Object[] objects = objsList.get(j);
				if (obj[i].equals(objects[0])) {
					incomingByRoomTypeView.setCheckinType(obj[i].toString());
					incomingByRoomTypeView.setCount(objects[1] == null ? 0 : Integer.parseInt(objects[1].toString()));
					incomingByRoomTypeView
							.setRoomPay(objects[2] == null ? 0 : Double.parseDouble(objects[2].toString()));
					incomingByRoomTypeView
							.setFoodPay(objects[3] == null ? 0 : Double.parseDouble(objects[3].toString()));
					incomingByRoomTypeView
							.setHealthPay(objects[4] == null ? 0 : Double.parseDouble(objects[4].toString()));
					incomingByRoomTypeView
							.setWaterPay(objects[5] == null ? 0 : Double.parseDouble(objects[5].toString()));
					incomingByRoomTypeView
							.setElectricPay(objects[6] == null ? 0 : Double.parseDouble(objects[6].toString()));
					incomingByRoomTypeView
							.setTrafficPay(objects[7] == null ? 0 : Double.parseDouble(objects[7].toString()));
					incomingByRoomTypeView
							.setPickupPay(objects[8] == null ? 0 : Double.parseDouble(objects[8].toString()));
					incomingByRoomTypeView
							.setOtherPay(objects[9] == null ? 0 : Double.parseDouble(objects[9].toString()));
					incomingByRoomTypeView
							.setAllPay(objects[10] == null ? 0 : Double.parseDouble(objects[10].toString()));
					testList.add(incomingByRoomTypeView);
				}
			}
			if (incomingByRoomTypeView.getCheckinType() == null) {
				incomingByRoomTypeView.setCheckinType(obj[i].toString());
				incomingByRoomTypeView.setCount(0);
				incomingByRoomTypeView.setRoomPay(0.0);
				incomingByRoomTypeView.setFoodPay(0.0);
				incomingByRoomTypeView.setHealthPay(0.0);
				incomingByRoomTypeView.setWaterPay(0.0);
				incomingByRoomTypeView.setElectricPay(0.0);
				incomingByRoomTypeView.setTrafficPay(0.0);
				incomingByRoomTypeView.setPickupPay(0.0);
				incomingByRoomTypeView.setOtherPay(0.0);
				incomingByRoomTypeView.setAllPay(0.0);
				testList.add(incomingByRoomTypeView);
			}
		}
		for (int i = 0; i < testList.size(); i++) {
			IncomingByRoomTypeView incomingByRoomTypeView = testList.get(i);
			if (i == 0) {
				incomingByRoomTypeView.setCheckinType("会员散客");
				viewList.add(incomingByRoomTypeView);
			}
			if (i == 1) {
				incomingByRoomTypeView.setCheckinType("旅游团队");
				viewList.add(incomingByRoomTypeView);
			}
			if (i == 2) {
				incomingByRoomTypeView.setCheckinType("旅游散客");
				viewList.add(incomingByRoomTypeView);
			}
		}
		return viewList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IncomingDetailsView> queryByRoomTypeDetails(String checkinType, Date startDate, Date endDate) {
		List<IncomingDetailsView> viewList = new ArrayList<>();
		if ("联盟团".equals(checkinType)) {
			StringBuffer sql1 = new StringBuffer();
			sql1.append(
					" SELECT osp.name ospname,td.`name` tdname,COUNT(trp.pkRoomPersonalInfo),tgr.checkInDate,tgr.checkOutDate, ");
			sql1.append(
					" sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END) AS roompay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END) AS foodpay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END) AS healthpay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)                                                            ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) ");
			sql1.append(" +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) AS waterpay, ");
			
			sql1.append(
					" sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END) AS electricpay,                                                  ");
			sql1.append(
					" sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END) AS trafficpay,                                                     ");
			sql1.append(
					" sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END) AS pickuppay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END) AS otherpay,                                                           ");
			sql1.append(
					"  sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END)+sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END)                ");
			sql1.append(
					" +sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END)+sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)          ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) ");
			sql1.append(
					" +sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END)+sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END)  ");
			sql1.append(
					" +sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END)+sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END)          ");
			sql1.append(
					"  FROM travel_roompersonalInfo trp                                                                      ");
			sql1.append(
					"  left JOIN                                                                                             ");
			sql1.append(
					"  travel_checkoutsettlement tcs                                                                         ");
			sql1.append(
					"  ON tcs.pkCheckOutSettlement=trp.pkCheckOutSettlement                                                  ");
			sql1.append(
					" left  JOIN travel_checkinimplement tci                                                                 ");
			sql1.append(
					"  ON tci.pkCIImplement=trp.pkCIImplement                                                                ");
			sql1.append(
					" left  JOIN travel_groupreserve tgr                                                                     ");
			sql1.append(
					"  ON tgr.pkGroupReserve=tci.pkGroupReserve                                                              ");
			sql1.append(
					" left JOIN travel_planitemgroup tpg                                                                     ");
			sql1.append(
					"  ON tpg.pkplanitemgroup=tgr.pkPlanItemGroup                                                            ");
			sql1.append(
					" left JOIN om_servicepoint osp                                                                          ");
			sql1.append(
					" ON osp.pkServicePoint=tci.pkServicePoint                                                               ");
			sql1.append(
					" LEFT JOIN travel_planitem tpi on tpi.pkPlanItem = tpg.pkPlanItem                                       ");
			sql1.append(
					" LEFT JOIN travel_destination td on tpi.pkDestination = td.pkDestination                                ");
			sql1.append(
					"  WHERE tci.statementDate >=:startDate AND tci.statementDate<=:endDate                              ");
			sql1.append(
					" and tpg.alliance =1                                                                                    ");
			sql1.append(
					" GROUP BY   tci.pkCIImplement                                                                    ");
			sql1.append(" ORDER BY  tgr.checkInDate    DESC                                                       ");
			List<Object[]> objList = getSession().createNativeQuery(sql1.toString()).setParameter("startDate", startDate)
					.setParameter("endDate", endDate).list();
			for (int i = 0; i < objList.size(); i++) {
				Object[] objects = objList.get(i);
				IncomingDetailsView incomingDetailsView = new IncomingDetailsView();
				incomingDetailsView.setEmployer(objects[0] == null ? "" : objects[0].toString());
				incomingDetailsView.setDestination(objects[1] == null ? "" : objects[1].toString());
				incomingDetailsView.setMemCount(objects[2] == null ? 0 : Integer.parseInt(objects[2].toString()));
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				try {
					if (objects[3] != null) {
						incomingDetailsView.setCheckinDate(df.parse(objects[3].toString()));
					}
					if (objects[4] != null) {
						incomingDetailsView.setCheckoutDate(df.parse(objects[4].toString()));
					}
				} catch (ParseException e) {
					e.printStackTrace();
				}
				incomingDetailsView.setRoomPay(objects[5] == null ? 0.0 : Double.parseDouble(objects[5].toString()));
				incomingDetailsView.setFoodPay(objects[6] == null ? 0.0 : Double.parseDouble(objects[6].toString()));
				incomingDetailsView.setHealthPay(objects[7] == null ? 0.0 : Double.parseDouble(objects[7].toString()));
				incomingDetailsView.setWaterPay(objects[8] == null ? 0.0 : Double.parseDouble(objects[8].toString()));
				incomingDetailsView
						.setElectricPay(objects[9] == null ? 0.0 : Double.parseDouble(objects[9].toString()));
				incomingDetailsView
						.setTrafficPay(objects[10] == null ? 0.0 : Double.parseDouble(objects[10].toString()));
				incomingDetailsView
						.setPickupPay(objects[11] == null ? 0.0 : Double.parseDouble(objects[11].toString()));
				incomingDetailsView.setOtherPay(objects[12] == null ? 0.0 : Double.parseDouble(objects[12].toString()));
				incomingDetailsView.setAllPay(objects[13] == null ? 0.0 : Double.parseDouble(objects[13].toString()));
				viewList.add(incomingDetailsView);
			}
		}
		if ("会员团".equals(checkinType)) {
			StringBuffer sql1 = new StringBuffer();
			sql1.append(
					" SELECT osp.name ospname,td.`name` tdname,COUNT(trp.pkRoomPersonalInfo),tgr.checkInDate,tgr.checkOutDate, ");
			sql1.append(
					" sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END) AS roompay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END) AS foodpay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END) AS healthpay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)                                                            ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) ");
			sql1.append(" +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) AS waterpay, ");
			sql1.append(
					" sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END) AS electricpay,                                                  ");
			sql1.append(
					" sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END) AS trafficpay,                                                     ");
			sql1.append(
					" sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END) AS pickuppay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END) AS otherpay,                                                           ");
			sql1.append(
					"  sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END)+sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END)                ");
			sql1.append(
					" +sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END)+sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)          ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) ");
			sql1.append(
					" +sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END)+sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END)  ");
			sql1.append(
					" +sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END)+sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END)          ");
			sql1.append(
					"  FROM travel_roompersonalInfo trp                                                                      ");
			sql1.append(
					"  left JOIN                                                                                             ");
			sql1.append(
					"  travel_checkoutsettlement tcs                                                                         ");
			sql1.append(
					"  ON tcs.pkCheckOutSettlement=trp.pkCheckOutSettlement                                                  ");
			sql1.append(
					" left  JOIN travel_checkinimplement tci                                                                 ");
			sql1.append(
					"  ON tci.pkCIImplement=trp.pkCIImplement                                                                ");
			sql1.append(
					" left  JOIN travel_groupreserve tgr                                                                     ");
			sql1.append(
					"  ON tgr.pkGroupReserve=tci.pkGroupReserve                                                              ");
			sql1.append(
					" left JOIN travel_planitemgroup tpg                                                                     ");
			sql1.append(
					"  ON tpg.pkplanitemgroup=tgr.pkPlanItemGroup                                                            ");
			sql1.append(
					" left JOIN om_servicepoint osp                                                                          ");
			sql1.append(
					" ON osp.pkServicePoint=tci.pkServicePoint                                                               ");
			sql1.append(
					" LEFT JOIN travel_planitem tpi on tpi.pkPlanItem = tpg.pkPlanItem                                       ");
			sql1.append(
					" LEFT JOIN travel_destination td on tpi.pkDestination = td.pkDestination                                ");
			sql1.append(
					"  WHERE tci.statementDate >=:startDate AND tci.statementDate<=:endDate                              ");
			sql1.append(
					" and tpg.alliance =0                                                                                    ");
			sql1.append(
					" GROUP BY   tci.pkCIImplement                                                                    ");
			sql1.append(" ORDER BY  tgr.checkInDate    DESC                                                       ");
			List<Object[]> objList = getSession().createNativeQuery(sql1.toString()).setParameter("startDate", startDate)
					.setParameter("endDate", endDate).list();
			for (int i = 0; i < objList.size(); i++) {
				Object[] objects = objList.get(i);
				IncomingDetailsView incomingDetailsView = new IncomingDetailsView();
				incomingDetailsView.setEmployer(objects[0] == null ? "" : objects[0].toString());
				incomingDetailsView.setDestination(objects[1] == null ? "" : objects[1].toString());
				incomingDetailsView.setMemCount(objects[2] == null ? 0 : Integer.parseInt(objects[2].toString()));
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				try {
					if (objects[3] != null) {
						incomingDetailsView.setCheckinDate(df.parse(objects[3].toString()));
					}
					if (objects[4] != null) {
						incomingDetailsView.setCheckoutDate(df.parse(objects[4].toString()));
					}
				} catch (ParseException e) {
					e.printStackTrace();
				}
				incomingDetailsView.setRoomPay(objects[5] == null ? 0.0 : Double.parseDouble(objects[5].toString()));
				incomingDetailsView.setFoodPay(objects[6] == null ? 0.0 : Double.parseDouble(objects[6].toString()));
				incomingDetailsView.setHealthPay(objects[7] == null ? 0.0 : Double.parseDouble(objects[7].toString()));
				incomingDetailsView.setWaterPay(objects[8] == null ? 0.0 : Double.parseDouble(objects[8].toString()));
				incomingDetailsView
						.setElectricPay(objects[9] == null ? 0.0 : Double.parseDouble(objects[9].toString()));
				incomingDetailsView
						.setTrafficPay(objects[10] == null ? 0.0 : Double.parseDouble(objects[10].toString()));
				incomingDetailsView
						.setPickupPay(objects[11] == null ? 0.0 : Double.parseDouble(objects[11].toString()));
				incomingDetailsView.setOtherPay(objects[12] == null ? 0.0 : Double.parseDouble(objects[12].toString()));
				incomingDetailsView.setAllPay(objects[13] == null ? 0.0 : Double.parseDouble(objects[13].toString()));
				viewList.add(incomingDetailsView);
			}
		}
		if ("会员散客".equals(checkinType)) {
			StringBuffer sql1 = new StringBuffer();
			sql1.append(
					" SELECT tng.groupUnit,'',COUNT(trp.pkRoomPersonalInfo),tng.checkInDate,tng.checkOutDate,          ");
			sql1.append(
					" sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END) AS roompay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END) AS foodpay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END) AS healthpay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)                                                            ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) ");
			sql1.append(" +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) AS waterpay, ");
			sql1.append(
					" sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END) AS electricpay,                                                  ");
			sql1.append(
					" sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END) AS trafficpay,                                                     ");
			sql1.append(
					" sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END) AS pickuppay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END) AS otherpay,                                                           ");
			sql1.append(
					"  sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END)+sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END)                ");
			sql1.append(
					" +sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END)+sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)          ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) ");
			sql1.append(
					" +sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END)+sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END)  ");
			sql1.append(
					" +sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END)+sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END)          ");
			sql1.append(" FROM                                                                                     ");
			sql1.append(" travel_checkinimplement tci                                                              ");
			sql1.append(" JOIN travel_nonmemgroupreserve tngr                                                      ");
			sql1.append(" ON tngr.pkNonMemGroupReserve=tci.pkNonMemGroupReserve                                    ");
			sql1.append(" JOIN travel_nonmembergroup tng                                                           ");
			sql1.append(" ON tng.pkNonMemberGroup=tngr.pkNonMemberGroup                                            ");
			sql1.append(" JOIN                                                                                     ");
			sql1.append(" travel_roompersonalInfo trp                                                              ");
			sql1.append(" ON trp.pkCIImplement=tci.pkCIImplement                                                   ");
			sql1.append("  JOIN                                                                                    ");
			sql1.append(" travel_checkoutsettlement tcs                                                            ");
			sql1.append(" ON tcs.pkCheckOutSettlement=trp.pkCheckOutSettlement                                     ");
			sql1.append(" JOIN om_servicepoint osp                                                                 ");
			sql1.append(" ON osp.pkServicePoint=tng.pkServicePoint                                                 ");
			sql1.append(" WHERE tci.statementDate >=:startDate AND tci.statementDate<=:endDate 				");
			sql1.append(" AND tng.tourType='IndivMember'                                                    ");
			sql1.append(" GROUP BY tng.pkNonMemberGroup                                                     ");
			sql1.append(" ORDER BY  tng.checkInDate    DESC                                                       ");
			List<Object[]> objList = getSession().createNativeQuery(sql1.toString()).setParameter("startDate", startDate)
					.setParameter("endDate", endDate).list();
			for (int i = 0; i < objList.size(); i++) {
				Object[] objects = objList.get(i);
				IncomingDetailsView incomingDetailsView = new IncomingDetailsView();
				incomingDetailsView.setEmployer(objects[0] == null ? "" : objects[0].toString());
				incomingDetailsView.setDestination(objects[1] == null ? "" : objects[1].toString());
				incomingDetailsView.setMemCount(objects[2] == null ? 0 : Integer.parseInt(objects[2].toString()));
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				try {
					if (objects[3] != null) {
						incomingDetailsView.setCheckinDate(df.parse(objects[3].toString()));
					}
					if (objects[4] != null) {
						incomingDetailsView.setCheckoutDate(df.parse(objects[4].toString()));
					}
				} catch (ParseException e) {
					e.printStackTrace();
				}
				incomingDetailsView.setRoomPay(objects[5] == null ? 0.0 : Double.parseDouble(objects[5].toString()));
				incomingDetailsView.setFoodPay(objects[6] == null ? 0.0 : Double.parseDouble(objects[6].toString()));
				incomingDetailsView.setHealthPay(objects[7] == null ? 0.0 : Double.parseDouble(objects[7].toString()));
				incomingDetailsView.setWaterPay(objects[8] == null ? 0.0 : Double.parseDouble(objects[8].toString()));
				incomingDetailsView
						.setElectricPay(objects[9] == null ? 0.0 : Double.parseDouble(objects[9].toString()));
				incomingDetailsView
						.setTrafficPay(objects[10] == null ? 0.0 : Double.parseDouble(objects[10].toString()));
				incomingDetailsView
						.setPickupPay(objects[11] == null ? 0.0 : Double.parseDouble(objects[11].toString()));
				incomingDetailsView.setOtherPay(objects[12] == null ? 0.0 : Double.parseDouble(objects[12].toString()));
				incomingDetailsView.setAllPay(objects[13] == null ? 0.0 : Double.parseDouble(objects[13].toString()));
				viewList.add(incomingDetailsView);
			}
		}
		if ("旅游团队".equals(checkinType)) {
			StringBuffer sql1 = new StringBuffer();
			sql1.append(
					" SELECT tng.groupUnit,'',COUNT(trp.pkRoomPersonalInfo),tng.checkInDate,tng.checkOutDate,          ");
			sql1.append(
					" sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END) AS roompay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END) AS foodpay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END) AS healthpay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)                                                            ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) ");
			sql1.append(" +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) AS waterpay, ");
			sql1.append(
					" sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END) AS electricpay,                                                  ");
			sql1.append(
					" sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END) AS trafficpay,                                                     ");
			sql1.append(
					" sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END) AS pickuppay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END) AS otherpay,                                                           ");
			sql1.append(
					"  sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END)+sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END)                ");
			sql1.append(
					" +sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END)+sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)          ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) ");
			sql1.append(
					" +sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END)+sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END)  ");
			sql1.append(
					" +sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END)+sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END)          ");
			sql1.append(" FROM                                                                                     ");
			sql1.append(" travel_checkinimplement tci                                                              ");
			sql1.append(" JOIN travel_nonmemgroupreserve tngr                                                      ");
			sql1.append(" ON tngr.pkNonMemGroupReserve=tci.pkNonMemGroupReserve                                    ");
			sql1.append(" JOIN travel_nonmembergroup tng                                                           ");
			sql1.append(" ON tng.pkNonMemberGroup=tngr.pkNonMemberGroup                                            ");
			sql1.append(" JOIN                                                                                     ");
			sql1.append(" travel_roompersonalInfo trp                                                              ");
			sql1.append(" ON trp.pkCIImplement=tci.pkCIImplement                                                   ");
			sql1.append("  JOIN                                                                                    ");
			sql1.append(" travel_checkoutsettlement tcs                                                            ");
			sql1.append(" ON tcs.pkCheckOutSettlement=trp.pkCheckOutSettlement                                     ");
			sql1.append(" JOIN om_servicepoint osp                                                                 ");
			sql1.append(" ON osp.pkServicePoint=tng.pkServicePoint                                                 ");
			sql1.append(" WHERE tci.statementDate >=:startDate AND tci.statementDate<=:endDate 				");
			sql1.append(" AND tng.tourType='TravelGroup'                                                    ");
			sql1.append(" GROUP BY tng.pkNonMemberGroup                                                     ");
			sql1.append(" ORDER BY  tng.checkInDate    DESC                                                       ");
			List<Object[]> objList = getSession().createNativeQuery(sql1.toString()).setParameter("startDate", startDate)
					.setParameter("endDate", endDate).list();
			for (int i = 0; i < objList.size(); i++) {
				Object[] objects = objList.get(i);
				IncomingDetailsView incomingDetailsView = new IncomingDetailsView();
				incomingDetailsView.setEmployer(objects[0] == null ? "" : objects[0].toString());
				incomingDetailsView.setDestination(objects[1] == null ? "" : objects[1].toString());
				incomingDetailsView.setMemCount(objects[2] == null ? 0 : Integer.parseInt(objects[2].toString()));
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				try {
					if (objects[3] != null) {
						incomingDetailsView.setCheckinDate(df.parse(objects[3].toString()));
					}
					if (objects[4] != null) {
						incomingDetailsView.setCheckoutDate(df.parse(objects[4].toString()));
					}
				} catch (ParseException e) {
					e.printStackTrace();
				}
				incomingDetailsView.setRoomPay(objects[5] == null ? 0.0 : Double.parseDouble(objects[5].toString()));
				incomingDetailsView.setFoodPay(objects[6] == null ? 0.0 : Double.parseDouble(objects[6].toString()));
				incomingDetailsView.setHealthPay(objects[7] == null ? 0.0 : Double.parseDouble(objects[7].toString()));
				incomingDetailsView.setWaterPay(objects[8] == null ? 0.0 : Double.parseDouble(objects[8].toString()));
				incomingDetailsView
						.setElectricPay(objects[9] == null ? 0.0 : Double.parseDouble(objects[9].toString()));
				incomingDetailsView
						.setTrafficPay(objects[10] == null ? 0.0 : Double.parseDouble(objects[10].toString()));
				incomingDetailsView
						.setPickupPay(objects[11] == null ? 0.0 : Double.parseDouble(objects[11].toString()));
				incomingDetailsView.setOtherPay(objects[12] == null ? 0.0 : Double.parseDouble(objects[12].toString()));
				incomingDetailsView.setAllPay(objects[13] == null ? 0.0 : Double.parseDouble(objects[13].toString()));
				viewList.add(incomingDetailsView);
			}
		}
		if ("旅游散客".equals(checkinType)) {
			StringBuffer sql1 = new StringBuffer();
			sql1.append(
					" SELECT tng.groupUnit,'',COUNT(trp.pkRoomPersonalInfo),tng.checkInDate,tng.checkOutDate,          ");
			sql1.append(
					" sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END) AS roompay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END) AS foodpay,                                                              ");
			sql1.append(
					" sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END) AS healthpay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)                                                            ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) ");
			sql1.append(" +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) AS waterpay, ");
			sql1.append(
					" sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END) AS electricpay,                                                  ");
			sql1.append(
					" sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END) AS trafficpay,                                                     ");
			sql1.append(
					" sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END) AS pickuppay,                                                        ");
			sql1.append(
					" sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END) AS otherpay,                                                           ");
			sql1.append(
					"  sum(CASE WHEN tcs.room!='NULL' THEN tcs.room ELSE 0 END)+sum(CASE WHEN tcs.food!='NULL' THEN tcs.food ELSE 0 END)                ");
			sql1.append(
					" +sum(CASE WHEN tcs.health!='NULL' THEN tcs.health ELSE 0 END)+sum(CASE WHEN tcs.coldWater!='NULL' THEN tcs.coldWater ELSE 0 END)          ");
			sql1.append(" +sum(CASE WHEN tcs.hotWater!='NULL' THEN tcs.hotWater ELSE 0 END) +sum(CASE WHEN tcs.waterOther!='NULL' THEN tcs.waterOther ELSE 0 END) ");
			sql1.append(
					" +sum(CASE WHEN tcs.electric!='NULL' THEN tcs.electric ELSE 0 END)+sum(CASE WHEN tcs.traffic!='NULL' THEN tcs.traffic ELSE 0 END)  ");
			sql1.append(
					" +sum(CASE WHEN tcs.pickUp!='NULL' THEN tcs.pickUp ELSE 0 END)+sum(CASE WHEN tcs.other!='NULL' THEN tcs.other ELSE 0 END)          ");
			sql1.append(" FROM                                                                                     ");
			sql1.append(" travel_checkinimplement tci                                                              ");
			sql1.append(" JOIN travel_nonmemgroupreserve tngr                                                      ");
			sql1.append(" ON tngr.pkNonMemGroupReserve=tci.pkNonMemGroupReserve                                    ");
			sql1.append(" JOIN travel_nonmembergroup tng                                                           ");
			sql1.append(" ON tng.pkNonMemberGroup=tngr.pkNonMemberGroup                                            ");
			sql1.append(" JOIN                                                                                     ");
			sql1.append(" travel_roompersonalInfo trp                                                              ");
			sql1.append(" ON trp.pkCIImplement=tci.pkCIImplement                                                   ");
			sql1.append("  JOIN                                                                                    ");
			sql1.append(" travel_checkoutsettlement tcs                                                            ");
			sql1.append(" ON tcs.pkCheckOutSettlement=trp.pkCheckOutSettlement                                     ");
			sql1.append(" JOIN om_servicepoint osp                                                                 ");
			sql1.append(" ON osp.pkServicePoint=tng.pkServicePoint                                                 ");
			sql1.append(" WHERE tci.statementDate >=:startDate AND tci.statementDate<=:endDate 				");
			sql1.append(" AND tng.tourType='Individual'                                                    ");
			sql1.append(" GROUP BY tng.pkNonMemberGroup                                                     ");
			sql1.append(" ORDER BY  tng.checkInDate    DESC                                                       ");
			List<Object[]> objList = getSession().createNativeQuery(sql1.toString()).setParameter("startDate", startDate)
					.setParameter("endDate", endDate).list();
			for (int i = 0; i < objList.size(); i++) {
				Object[] objects = objList.get(i);
				IncomingDetailsView incomingDetailsView = new IncomingDetailsView();
				incomingDetailsView.setEmployer(objects[0] == null ? "" : objects[0].toString());
				incomingDetailsView.setDestination(objects[1] == null ? "" : objects[1].toString());
				incomingDetailsView.setMemCount(objects[2] == null ? 0 : Integer.parseInt(objects[2].toString()));
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				try {
					if (objects[3] != null) {
						incomingDetailsView.setCheckinDate(df.parse(objects[3].toString()));
					}
					if (objects[4] != null) {
						incomingDetailsView.setCheckoutDate(df.parse(objects[4].toString()));
					}
				} catch (ParseException e) {
					e.printStackTrace();
				}
				incomingDetailsView.setRoomPay(objects[5] == null ? 0.0 : Double.parseDouble(objects[5].toString()));
				incomingDetailsView.setFoodPay(objects[6] == null ? 0.0 : Double.parseDouble(objects[6].toString()));
				incomingDetailsView.setHealthPay(objects[7] == null ? 0.0 : Double.parseDouble(objects[7].toString()));
				incomingDetailsView.setWaterPay(objects[8] == null ? 0.0 : Double.parseDouble(objects[8].toString()));
				incomingDetailsView
						.setElectricPay(objects[9] == null ? 0.0 : Double.parseDouble(objects[9].toString()));
				incomingDetailsView
						.setTrafficPay(objects[10] == null ? 0.0 : Double.parseDouble(objects[10].toString()));
				incomingDetailsView
						.setPickupPay(objects[11] == null ? 0.0 : Double.parseDouble(objects[11].toString()));
				incomingDetailsView.setOtherPay(objects[12] == null ? 0.0 : Double.parseDouble(objects[12].toString()));
				incomingDetailsView.setAllPay(objects[13] == null ? 0.0 : Double.parseDouble(objects[13].toString()));
				viewList.add(incomingDetailsView);
			}
		}
		return viewList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IncomingStatisticsView> queryByType(Date startDate, Date endDate) {

		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT a,b,count(c),sum(d) from (  ");
		sql.append(
				"  SELECT trt.pkRoomType a,trt.`name` b,count(tcs.pkCheckOutSettlement) c,                                         ");
		sql.append(
				"  sum(CASE WHEN tcs.room!='NULL' THEN tcs.room*CASE WHEN TIMESTAMPDIFF(DAY,tc.checkInDate,tc.checkOutDate)<=1 THEN 1 ELSE TIMESTAMPDIFF(DAY,tc.checkInDate,tc.checkOutDate) END END) d");
		sql.append("  FROM travel_roomtype trt");
		sql.append("  LEFT JOIN bd_room br                                                                     ");
		sql.append("  ON br.pkRoomType=trt.pkRoomType                                                          ");
		sql.append("  LEFT JOIN travel_roomregister trr                                                        ");
		sql.append("  ON trr.pkRoom=br.pkRoom                                                                  ");
		sql.append("  LEFT JOIN travel_roompersonalInfo trp                                                    ");
		sql.append("  on trp.pkRoomRegister=trr.pkRoomRegister                                                 ");
		sql.append("  LEFT JOIN travel_checkinimplement tci                                                    ");
		sql.append("  ON tci.pkCIImplement=trp.pkCIImplement                                                   ");
		sql.append("  LEFT JOIN travel_checkoutsettlement tcs                                                  ");
		sql.append("  ON tcs.pkCheckOutSettlement=trp.pkCheckOutSettlement                                     ");
		sql.append(
				" LEFT JOIN travel_checkin tc                                                                                     ");
		sql.append(
				" ON  trr.pkRoomRegister = tc.pkRoomRegister                                                                      ");
		sql.append("  WHERE tci.statementDate >=:startDate AND tci.statementDate<=:endDate                 ");
		sql.append(" GROUP BY br.pkRoom,tci.pkCIImplement )  t");
		sql.append("  GROUP BY a                                                                 ");
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("startDate", startDate)
				.setParameter("endDate", endDate).list();

		String sql1 = "SELECT trt.`name`,trt.pkRoomType FROM travel_roomtype trt";
		List<Object[]> list = getSession().createNativeQuery(sql1).list();

		StringBuffer sql2 = new StringBuffer();
		sql2.append(" SELECT a,b,count(c),sum(d) from( ");
		sql2.append(
				" SELECT                                                                                          ");
		sql2.append(
				" 	trt.pkRoomType a,                                                                              ");
		sql2.append(
				" 	trt. NAME b,                                                                                   ");
		sql2.append(
				" 	count(tcs.pkCheckOutSettlement) c,                                                             ");
		sql2.append("  	sum(                                                                                         ");
		sql2.append("  		CASE                                                                                     ");
		sql2.append(" 		WHEN tcs.room != 'NULL' THEN                                                             ");
		sql2.append("    	tcs.room    ");
		sql2.append("  	ELSE                                                                                         ");
		sql2.append("  			0                                                                                    ");
		sql2.append(
				"   		END                                                                                      ");
		sql2.append("  	) d");
		sql2.append(
				" FROM                                                                                            ");
		sql2.append(" 	travel_roomtype trt                                                                          ");
		sql2.append(
				" LEFT JOIN bd_room br ON br.pkRoomType = trt.pkRoomType                                          ");
		sql2.append(
				"  LEFT JOIN travel_roomregister trr ON trr.pkRoom = br.pkRoom                                    ");
		sql2.append(
				"  LEFT JOIN travel_roompersonalInfo trp ON trp.pkRoomRegister = trr.pkRoomRegister               ");
		sql2.append(
				"  LEFT JOIN travel_checkinimplement tci ON tci.pkCIImplement = trp.pkCIImplement                 ");
		sql2.append(
				"  LEFT JOIN travel_checkoutsettlement tcs ON tcs.pkCheckOutSettlement = trp.pkCheckOutSettlement ");
		sql2.append(
				" LEFT JOIN travel_checkoutdeal tcd ON tcd.pkCheckInImplement=tci.pkCIImplement                   ");
		sql2.append(" LEFT JOIN travel_nonmemgroupreserve tngr ON tngr.pkNonMemGroupReserve=tci.pkNonMemGroupReserve ");
		sql2.append(" LEFT JOIN travel_nonmembergroup tng ON tng.pkNonMemberGroup=tngr.pkNonMemberGroup              ");
		sql2.append(
				" WHERE                                                                                           ");
		sql2.append(" 	tcd.settlementDate >=:startDate                                                              ");
		sql2.append(
				" AND tcd.settlementDate <=:endDate                                                               ");
		sql2.append(" GROUP BY br.pkRoom,tcd.pkCheckOutDeal ) t ");
		sql2.append(
				"  GROUP BY                                                                                       ");
		sql2.append(" 	a                                                                               ");
		List<Object[]> objList2 = getSession().createNativeQuery(sql2.toString())// 散客的结算日期不同，重新查询
				.setParameter("startDate", startDate).setParameter("endDate", endDate).list();

		List<IncomingStatisticsView> viewList = new ArrayList<>();
		for (int i = 0; i < list.size(); i++) {
			Object[] objects = list.get(i);
			IncomingStatisticsView incomingStatisticsView = new IncomingStatisticsView();
			for (int j = 0; j < objList.size(); j++) {
				Object[] obj = objList.get(j);
				if ((objects[1] == null ? 0 : Integer.parseInt(objects[1].toString())) == (obj[0] == null ? 0
						: Integer.parseInt(obj[0].toString()))) {
					incomingStatisticsView.setRoomType(objects[0] == null ? "" : objects[0].toString());
					incomingStatisticsView.setCount(obj[2] == null ? 0 : Integer.parseInt(obj[2].toString()));
					incomingStatisticsView.setIncoming(obj[3] == null ? 0 : Double.parseDouble(obj[3].toString()));
					incomingStatisticsView.setPkRoomType(obj[0] == null ? 0 : Integer.parseInt(obj[0].toString()));
					viewList.add(incomingStatisticsView);
				}
			}
			if (incomingStatisticsView.getRoomType() == null) {
				incomingStatisticsView.setRoomType(objects[0] == null ? "" : objects[0].toString());
				incomingStatisticsView.setCount(0);
				incomingStatisticsView.setIncoming(0.0);
				incomingStatisticsView.setPkRoomType(objects[1] == null ? 0 : Integer.parseInt(objects[1].toString()));
				viewList.add(incomingStatisticsView);
			}
		}

		// 将散客情况添加到viewList中
		for (int i = 0; i < viewList.size(); i++) {
			IncomingStatisticsView incomingStatisticsView = viewList.get(i);
			for (int j = 0; j < objList2.size(); j++) {
				Object[] objects = objList2.get(j);
				if (incomingStatisticsView.getPkRoomType() == Integer.parseInt(objects[0].toString())) {
					incomingStatisticsView
							.setCount(incomingStatisticsView.getCount() + Integer.parseInt(objects[2].toString()));
					incomingStatisticsView.setIncoming(
							incomingStatisticsView.getIncoming() + Double.parseDouble(objects[3].toString()));
					break;
				}
			}

		}
		return viewList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IncomingTypeDetailsView> queryByTypeDetails(Date startDate, Date endDate, Long pkRoomType) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a,b,c,d,sum(e) from ( ");
		sql.append(
				" SELECT bb.`name` a,br.code b,tc.checkInDate c, tc.checkOutDate d,                                            ");
		sql.append(
				"  CASE WHEN tcs.room!='NULL' THEN tcs.room*CASE WHEN TIMESTAMPDIFF(DAY,tc.checkInDate,tc.checkOutDate)<=1 ");
		sql.append(
				" THEN 1 ELSE TIMESTAMPDIFF(DAY,tc.checkInDate,tc.checkOutDate) END END e,trr.pkRoomRegister f  ,br.pkRoom g ,tci.pkCIImplement  h            ");
		sql.append(
				" FROM bd_room br                                                                                                 ");
		sql.append(
				" LEFT JOIN travel_roomtype trt                                                                                   ");
		sql.append(
				" ON br.pkRoomType=trt.pkRoomType                                                                                 ");
		sql.append(
				" LEFT JOIN travel_roomregister                                                                                   ");
		sql.append(
				" trr ON trr.pkRoom=br.pkRoom LEFT JOIN travel_roompersonalInfo trp on trp.pkRoomRegister=trr.pkRoomRegister      ");
		sql.append(
				" LEFT JOIN travel_checkinimplement tci ON tci.pkCIImplement=trp.pkCIImplement LEFT JOIN travel_checkoutsettlement");
		sql.append(
				" tcs ON tcs.pkCheckOutSettlement=trp.pkCheckOutSettlement                                                        ");
		sql.append(
				" LEFT JOIN travel_checkin tc                                                                                     ");
		sql.append(
				" ON tc.pkCheckInImplement=tci.pkCIImplement                                                                      ");
		sql.append(
				" LEFT JOIN bd_building bb                                                                                        ");
		sql.append(
				" ON bb.pkOrgBuilding=br.pkOrgBuilding                                                                            ");
		sql.append(
				" WHERE tci.statementDate >=:startDate                                                                          ");
		sql.append(" AND tci.statementDate<=:endDate AND trt.pkRoomType=:pkRoomType                           ");
		sql.append(
				" GROUP BY trp.pkRoomPersonalInfo  ) t group by g,h                                                                                 ");
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("startDate", startDate)
				.setParameter("endDate", endDate).setParameter("pkRoomType", pkRoomType).list();
		List<IncomingTypeDetailsView> viewList = new ArrayList<>();
		if (objList != null) {
			for (int i = 0; i < objList.size(); i++) {
				IncomingTypeDetailsView incomingTypeDetailsView = new IncomingTypeDetailsView();
				Object[] objects = objList.get(i);
				incomingTypeDetailsView.setRoomCode((objects[0] == null ? "" : objects[0].toString()) + "-"
						+ (objects[1] == null ? "" : objects[1].toString()));
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				try {
					if (objects[2] != null) {
						incomingTypeDetailsView.setCheckinDate(df.parse(objects[2].toString()));
					}
					if (objects[3] != null) {
						incomingTypeDetailsView.setCheckoutDate(df.parse(objects[3].toString()));
					}
				} catch (ParseException e) {
					e.printStackTrace();
				}
				incomingTypeDetailsView
						.setIncoming(objects[4] == null ? 0.0 : Double.parseDouble(objects[4].toString()));
				viewList.add(incomingTypeDetailsView);
			}
		}

		StringBuffer sql1 = new StringBuffer();
		sql1.append(" SELECT a,b,c,d,sum(e) from ( ");
		sql1.append(" SELECT                                                                                         ");
		sql1.append(
				" 	bb.`name` a,                                                                                   ");
		sql1.append(
				" 	br. CODE b,                                                                                    ");
		sql1.append(
				" 	tng.checkInDate c,                                                                             ");
		sql1.append(
				" tng.checkOutDate d,                                                                              ");
		sql1.append(" 	tcs.room  e,    br.pkRoom  f    ,  tcd.pkCheckOutDeal  g                                ");
		sql1.append(" FROM                                                                                           ");
		sql1.append(" 	bd_room br                                                                                   ");
		sql1.append(" LEFT JOIN travel_roomtype trt ON br.pkRoomType = trt.pkRoomType                                ");
		sql1.append(" LEFT JOIN travel_roomregister trr ON trr.pkRoom = br.pkRoom                                    ");
		sql1.append(" LEFT JOIN travel_roompersonalInfo trp ON trp.pkRoomRegister = trr.pkRoomRegister               ");
		sql1.append(" LEFT JOIN travel_checkinimplement tci ON tci.pkCIImplement = trp.pkCIImplement                 ");
		sql1.append(" LEFT JOIN travel_checkoutsettlement tcs ON tcs.pkCheckOutSettlement = trp.pkCheckOutSettlement ");
		sql1.append(" LEFT JOIN travel_checkin tc ON tc.pkCheckInImplement = tci.pkCIImplement                       ");
		sql1.append(" LEFT JOIN bd_building bb ON bb.pkOrgBuilding = br.pkOrgBuilding                                ");
		sql1.append(" LEFT JOIN travel_checkoutdeal tcd ON tcd.pkCheckInImplement=tci.pkCIImplement                  ");
		sql1.append(" LEFT JOIN travel_nonmemgroupreserve tngr ON tngr.pkNonMemGroupReserve=tci.pkNonMemGroupReserve ");
		sql1.append(" LEFT JOIN travel_nonmembergroup tng ON tng.pkNonMemberGroup=tngr.pkNonMemberGroup              ");
		sql1.append(" WHERE                                                                                          ");
		sql1.append(" 	tcd.settlementDate >= :startDate                                                  ");
		sql1.append(" AND tcd.settlementDate <= :endDate                                                ");
		sql1.append(" AND trt.pkRoomType = :pkRoomType                                                            ");
		sql1.append("  GROUP BY                                                                                      ");
		sql1.append(
				" 	trp.pkRoomPersonalInfo )t      GROUP BY g,f                                                       ");
		List<Object[]> objList1 = getSession().createNativeQuery(sql1.toString()).setParameter("startDate", startDate)
				.setParameter("endDate", endDate).setParameter("pkRoomType", pkRoomType).list();
		if (objList1 != null) {
			for (int i = 0; i < objList1.size(); i++) {
				IncomingTypeDetailsView incomingTypeDetailsView = new IncomingTypeDetailsView();
				Object[] objects = objList1.get(i);
				incomingTypeDetailsView.setRoomCode((objects[0] == null ? "" : objects[0].toString()) + "-"
						+ (objects[1] == null ? "" : objects[1].toString()));
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				try {
					if (objects[2] != null) {
						incomingTypeDetailsView.setCheckinDate(df.parse(objects[2].toString()));
					}
					if (objects[3] != null) {
						incomingTypeDetailsView.setCheckoutDate(df.parse(objects[3].toString()));
					}
				} catch (ParseException e) {
					e.printStackTrace();
				}
				incomingTypeDetailsView
						.setIncoming(objects[4] == null ? 0.0 : Double.parseDouble(objects[4].toString()));
				viewList.add(incomingTypeDetailsView);
			}
		}

		return viewList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<OrderRoomView> queryOrderRoom() {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); 
		//获取当前月第一天：
        Calendar c = Calendar.getInstance();    
        c.add(Calendar.MONTH, 0);
        c.set(Calendar.DAY_OF_MONTH,1);//设置为1号,当前日期既为本月第一天 
        String startDate = format.format(c.getTime());
        c.add(Calendar.MONTH, 1);//下个月第一天
        String endDate = format.format(c.getTime());
        
        Calendar a = Calendar.getInstance();  
        a.set(Calendar.DATE, 1);  
        a.roll(Calendar.DATE, -1);  
        int maxDate = a.get(Calendar.DATE); 
        
        
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT                                                                   ");
		sql.append(" 	DAY(occupyDate),count(*)                                              ");
		sql.append(" FROM                                                                     ");
		sql.append(" 	`travel_roomoccupancy`                                                ");
		sql.append(" WHERE                                                                    ");
		sql.append(" 	1 = 1                                                                 ");
		sql.append(" and                                                                      ");
		sql.append(" occupyDate>=:startDate AND occupyDate<:endDate  ");
		sql.append(" GROUP  BY                                                                ");
		sql.append(" 	DAY(occupyDate);                                                      ");
		
		List<Object[]> objects = getSession().createNativeQuery(sql.toString())
				.setParameter("startDate", startDate).setParameter("endDate", endDate).list();
		List<OrderRoomView> viewList = new ArrayList<>();
		for(int i = 1; i <= maxDate; i ++){
			OrderRoomView orderRoomView = new OrderRoomView();
			for(int j = 0; j < objects.size(); j ++){
				Object[] o = objects.get(j);
				if(Integer.parseInt(o[0].toString())==i){
					orderRoomView.setDay(o[0]==null?0:Integer.parseInt(o[0].toString()));
					orderRoomView.setRoomCount(o[1]==null?0:Integer.parseInt(o[1].toString()));
					break;
				}
				if(j == objects.size()-1){
					orderRoomView.setDay(i);
					orderRoomView.setRoomCount(0);
				}
			}
			viewList.add(orderRoomView);
		}
		
		return viewList;
	}
}
